﻿using DapperExtensions;
using Interface.Cache;
using Interface.Core.Controllers;
using Interface.Core.Utility;
using Interface.Dapper;
using Interface.Mvc.Areas.Scrm.Models;
using Interface.Utility.Helper;
using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;

namespace Interface.Mvc.Areas.Scrm.Controllers
{
    public class MemberController : CoreController
    {
        private IEnumerable<MemberSource> Sources
        {
            get
            {
                return CommonCache.GetCache("Scrm_Member_Sources", () =>
                {
                    return db.Scrm.GetList<MemberSource>();
                });
            }
        }

        /// <summary>
        /// 福利社渠道会员信息存储
        /// url: /scrm/member/entrance
        /// params: source(taste), mobile, name, gender(m/f/u), email, openid, city, nickname, headurl, sourceremark
        /// </summary>
        private string TasteEntrance(int sourceid, string mobile, string name, string gender, string email, string openid, string city, string nickname, string headurl, string sourceremark)
        {
            int infoId = 0;

            try
            {
                #region 关联信息

                infoId = db.Scrm.Count(@"SELECT [MemberID] FROM [Member_Relate] 
                    WHERE [SourceID] = @SourceID AND [CharRelate] = @OpenID",
                    new { SourceID = sourceid, OpenID = openid });

                // 微信用户是否存在
                if (infoId > 0)
                {
                    return ResultUtility.SuccessResult("openid is exist", infoId);
                }

                var relate = new MemberRelate
                {
                    SourceID = sourceid,
                    CharRelate = openid,
                    NickName = nickname,
                    IncludeDate = DateTime.Now
                };

                #endregion

                db.Scrm.BeginTransaction();

                #region 主要信息

                // 手机号码查询用户编号
                infoId = db.Scrm.ExecuteScalar<int>("SELECT [ID] FROM [Member_Info] WHERE [Mobile] = @Mobile", new { Mobile = mobile });

                if (infoId == 0)
                {
                    var info = new MemberInfo
                    {
                        Mobile = mobile,
                        Name = name,
                        Gender = gender,
                        EMail = email,
                        City = city,
                        IncludeDate = DateTime.Now
                    };

                    relate.MemberID = infoId = db.Scrm.Insert(info);

                    #region 电访信息

                    dynamic re = db.Crm.StoredQuery<dynamic>("Scrm_Sp_RegCrm", new
                    {
                        source = sourceid,
                        mobile = mobile,
                        name = name,
                        gender = gender,
                        email = email,
                        remark = sourceremark
                    }).FirstOrDefault();

                    if (re.success > 0)
                    {
                        var relateCrm = new MemberRelate
                        {
                            SourceID = Sources.FirstOrDefault(m => m.Code == "crm").ID,
                            MemberID = relate.MemberID,
                            IntRelate = Convert.ToInt32(re.data),
                            IncludeDate = DateTime.Now
                        };
                        if (re.points > 0)
                        {
                            Dictionary<string, object> dic = new Dictionary<string, object>();
                            dic.Add("source", Sources.FirstOrDefault(m => m.Code == appid).Name);
                            dic.Add("scrmid", infoId);
                            dic.Add("openid", openid);
                            dic.Add("points", re.points);
                            dic.Add("remark", "首次注册赠送微笑值");
                            HttpHelper.Post("https://www.uvmami.com/interface/pointschangetemplate?key=engnice", dic);
                        }
                        db.Scrm.Insert(relateCrm);
                    }
                    else { db.Scrm.Rollback(); return ResultUtility.FailureResult("原CRM绑定失败!"); }

                    #endregion
                }
                else { relate.MemberID = infoId; }

                #endregion

                #region 扩展信息

                var relateExt = new MemberRelateExt { HeadUrl = headurl, SourceRemark = sourceremark };

                relateExt.RelateID = db.Scrm.Insert(relate);

                db.Scrm.Insert(relateExt);

                #endregion

                db.Scrm.Commit();
            }
            catch (Exception ex)
            {
                db.Scrm.Rollback(); throw ex;
            }

            return ResultUtility.SuccessResult(data: infoId);
        }

        /// <summary>
        /// 老CRM渠道会员信息存储
        /// url: /scrm/member/entrance
        /// params: source(crm), mobile, name, gender(m/f/u), email, city, sourceremark
        /// </summary>
        private string CrmEntrance(int sourceid, string mobile, string name, string gender, string email, string city, string sourceremark)
        {
            int infoId = 0;

            try
            {
                #region 主要信息

                // 手机号码查询用户编号
                infoId = db.Scrm.ExecuteScalar<int>("SELECT [ID] FROM [Member_Info] WHERE [Mobile] = @Mobile", new { Mobile = mobile });

                if (infoId == 0)
                {
                    var info = new MemberInfo
                    {
                        Mobile = mobile,
                        Name = name,
                        Gender = gender,
                        EMail = email,
                        City = city,
                        IncludeDate = DateTime.Now
                    };

                    db.Scrm.BeginTransaction();

                    infoId = db.Scrm.Insert(info);

                    #region 电访信息

                    dynamic re = db.Crm.StoredQuery<dynamic>("Scrm_Sp_RegCrm", new
                    {
                        source = sourceid,
                        mobile = mobile,
                        name = name,
                        gender = gender,
                        email = email,
                        remark = sourceremark
                    }).FirstOrDefault();

                    if (re.success > 0)
                    {
                        var relateCrm = new MemberRelate
                        {
                            SourceID = Sources.FirstOrDefault(m => m.Code == "crm").ID,
                            MemberID = infoId,
                            IntRelate = Convert.ToInt32(re.data),
                            IncludeDate = DateTime.Now
                        };
                        db.Scrm.Insert(relateCrm);
                    }
                    else { db.Scrm.Rollback(); return ResultUtility.FailureResult("原CRM绑定失败!"); }

                    #endregion

                    db.Scrm.Commit();
                }

                #endregion
            }
            catch (Exception ex)
            {
                db.Scrm.Rollback(); throw ex;
            }

            return ResultUtility.SuccessResult(data: infoId);
        }

        /// <summary>
        /// 导购引流会员信息存储
        /// url: /scrm/member/entrance
        /// params: source(appGuide), mobile, crmid, name, gender(m/f/u), email, openid, city
        /// </summary>
        private string AppGuideEntrance(int sourceid, string mobile, int crmid, string name, string gender, string email, string openid, string city)
        {
            int infoId = 0;

            try
            {
                #region 关联信息

                infoId = db.Scrm.Count(@"SELECT [MemberID] FROM [Member_Relate] 
                    WHERE [SourceID] = @SourceID AND [CharRelate] = @OpenID",
                    new { SourceID = sourceid, OpenID = openid });

                // 微信用户是否存在
                if (infoId > 0)
                {
                    return ResultUtility.SuccessResult("openid is exist", infoId);
                }

                var relate = new MemberRelate
                {
                    SourceID = sourceid,
                    CharRelate = openid,
                    NickName = "导购引流",
                    IncludeDate = DateTime.Now
                };

                #endregion

                db.Scrm.BeginTransaction();

                #region 主要信息

                // 手机号码查询用户编号
                infoId = db.Scrm.ExecuteScalar<int>("SELECT [ID] FROM [Member_Info] WHERE [Mobile] = @Mobile", new { Mobile = mobile });

                if (infoId == 0)
                {
                    var info = new MemberInfo
                    {
                        Mobile = mobile,
                        Name = name,
                        Gender = gender,
                        EMail = email,
                        City = city,
                        IncludeDate = DateTime.Now
                    };

                    relate.MemberID = infoId = db.Scrm.Insert(info);

                    #region 电访信息

                    var relateCrm = new MemberRelate
                    {
                        SourceID = Sources.FirstOrDefault(m => m.Code == "crm").ID,
                        MemberID = relate.MemberID,
                        IntRelate = crmid,
                        IncludeDate = DateTime.Now
                    };

                    db.Scrm.Insert(relateCrm);

                    #endregion
                }
                else { relate.MemberID = infoId; }

                db.Scrm.Insert(relate);

                #endregion

                db.Scrm.Commit();
            }
            catch (Exception ex)
            {
                db.Scrm.Rollback(); throw ex;
            }

            return ResultUtility.SuccessResult(data: infoId);
        }

        /// <summary>
        /// 解除某渠道的绑定
        /// url: /scrm/member/unbind
        /// params: source, id
        /// </summary>
        public string UnBind(string source, int id)
        {
            int sourceid = Sources.FirstOrDefault(m => m.Code == source).ID;

            string sql = "delete from Member_Relate where SourceID = @sourceid and MemberID = @memberid";

            int result = db.Scrm.Execute(sql, new { sourceid, memberid = id });

            return ResultUtility.UnknownResult(result > 0);
        }

        /// <summary>
        /// 会员信息总入口存储
        /// </summary>
        [HttpPost]
        public string Entrance(string source, string mobile, int? crmid, string name, string gender, string email, string openid, string city, string nickname, string headurl, string sourceremark)
        {
            int sourceid = Sources.FirstOrDefault(m => m.Code == source).ID;

            switch (source)
            {
                case "taste": return TasteEntrance(sourceid, mobile, name, gender, email, openid, city, nickname, headurl, sourceremark);
                case "crm": return CrmEntrance(sourceid, mobile, name, gender, email, city, sourceremark);
                case "appGuide": return AppGuideEntrance(sourceid, mobile, crmid.Value, name, gender, email, openid, city);
                default: return ResultUtility.FailureResult("未开通的渠道入口");
            }
        }

        /// <summary>
        /// 修改会员信息
        /// url: /scrm/member/update
        /// params: id,mobile,name,gender,email,city
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public string Update(int id, string mobile, string name, string gender, string email, string city, string pregnantState, string pregnantTime)
        {
            if (id <= 0) { return ResultUtility.FailureResult(); }

            StringBuilder sql = new StringBuilder();

            dynamic obj = new ExpandoObject();

            sql.Append("update Member_Info set");

            if (!string.IsNullOrEmpty(mobile))
            {
                sql.Append(" mobile=@mobile,"); obj.mobile = mobile;
            }
            if (!string.IsNullOrEmpty(name))
            {
                sql.Append(" name=@name,"); obj.name = name;
            }
            if (!string.IsNullOrEmpty(gender))
            {
                sql.Append(" gender=@gender,"); obj.gender = gender;
            }
            if (!string.IsNullOrEmpty(email))
            {
                sql.Append(" email=@email,"); obj.email = email;
            }
            if (!string.IsNullOrEmpty(pregnantState))
            {
                sql.Append(" pregnantState=@pregnantState,"); obj.pregnantState = pregnantState;
            }
            if (!string.IsNullOrEmpty(pregnantTime))
            {
                sql.Append(" pregnantTime=@pregnantTime,"); obj.PregnantTime = pregnantTime;
            }
            if (!string.IsNullOrEmpty(city))
            {
                sql.Append(" city=@city"); obj.city = city;
            }

            obj.id = id;

            int result = db.Scrm.Execute(sql.ToStr().TrimEnd(',') + " where id=@id", obj);

            if (result > 0)
            {
                return ResultUtility.SuccessResult();
            }
            else { return ResultUtility.FailureResult(); }
        }


        /// <summary>
        /// 根据编号获取会员信息
        /// url: /scrm/member/getmemberbyid
        /// params: id
        /// </summary>
        [HttpGet]
        public string GetMemberByID(int id)
        {
            MemberInfo mi = db.Scrm.Get<MemberInfo>(id);

            if (mi != null)
            {
                return ResultUtility.SuccessResult(data: mi);
            }
            else { return ResultUtility.FailureResult("未找到相应的会员!"); }
        }

        /// <summary>
        /// 根据手机号码获取会员信息
        /// url: /scrm/member/getmemberbymobile
        /// params: mobile
        /// </summary>
        [HttpGet]
        public string GetMemberByMobile(string mobile)
        {
            string sql = "SELECT * FROM [Member_Info] WHERE [Mobile] = @Mobile";

            MemberInfo mi = db.Scrm.Query<MemberInfo>(sql, new { Mobile = mobile }).FirstOrDefault();

            if (mi != null)
            {
                return ResultUtility.SuccessResult(data: mi);
            }
            else { return ResultUtility.FailureResult("未找到相应的会员!"); }
        }

        /// <summary>
        /// 根据手机号码获取会员编号
        /// url: /scrm/member/getmemberidbymobile
        /// params: mobile
        /// </summary>
        [HttpGet]
        public string GetMemberIDByMobile(string mobile)
        {
            int id = db.Scrm.ExecuteScalar<int>("SELECT [ID] FROM [Member_Info] WHERE [Mobile] = @Mobile", new { Mobile = mobile });

            if (id > 0)
            {
                return ResultUtility.SuccessResult(data: id);
            }
            else { return ResultUtility.FailureResult("未找到相应的会员!"); }
        }

        /// <summary>
        /// 根据编号删除
        /// url: /scrm/member/delete
        /// params: id
        /// </summary>
        [HttpPost]
        public string Delete(int id)
        {
            var predicate = Predicates.Field<MemberInfo>(f => f.ID, Operator.Eq, id);

            return ResultUtility.UnknownResult(db.Scrm.Delete<MemberInfo>(predicate));
        }

        /// <summary>
        /// 注册基础统计数据
        /// url: /scrm/member/statisticsregisterbasic
        /// </summary>
        [HttpGet]
        public string StatisticsRegisterBasic()
        {
            string sql = @"Declare @Today datetime = CONVERT(varchar, GETDATE(), 23)
                Declare @Yesterday datetime = DATEADD(DAY, -1, @Today)
                select t1.iCount, t2.iAverage, t3.iTotal from (
                select COUNT(1) as iCount from Member_Info
                where CONVERT(varchar, IncludeDate, 23) = @Yesterday) as t1,(
                select AVG(t.c) as iAverage from (
                select Count(1) as c from Member_Info
                where IncludeDate < @Today
                group by CONVERT(varchar, IncludeDate, 23)) as t) as t2,(
                select Count(1) as iTotal from Member_Info
                where IncludeDate < @Today) as t3";

            return ResultUtility.SuccessResult(data: db.Scrm.Query<dynamic>(sql).FirstOrDefault());
        }

        /// <summary>
        /// 注册时间区间来源分布
        /// url: /scrm/member/statisticsregistersource
        /// params: begintime, endtime
        /// </summary>
        [HttpPost]
        public string StatisticsRegisterSource(DateTime begintime, DateTime endtime)
        {
            string time1 = begintime.ToString("yyyy-MM-dd");
            string time2 = endtime.AddDays(1).ToString("yyyy-MM-dd");

            string sql = @";With CTE As(
            Select CONVERT(varchar,t2.IncludeDate,23) as iTime,
            iUrl=SUBSTRING(t1.SourceRemark+'?',0,CHARINDEX('?',t1.SourceRemark+'?'))
            From Member_RelateExt t1(nolock),Member_Relate t2(nolock)
            Where t1.RelateID=t2.ID And t2.IncludeDate>=@time1 And t2.IncludeDate<@time2)
            Select iTime,iUrl,iCount=COUNT(1) 
            From CTE Group By iTime,iUrl Order By iTime Desc";

            return ResultUtility.SuccessResult(data: db.Scrm.Query<dynamic>(sql, new { time1, time2 }));
        }
    }
}